﻿/*
'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.
'Description:撤销模块
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2020-10-22
*/
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
//using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
  public  class uMESRevokeBusiness
    {
        uMESCommonBusiness commonBal = new uMESCommonBusiness();

        /// <summary>
        /// 查询批次基本信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetContainerBase(Dictionary<string,string> para) {
            string strSql = @"select m.processno,c.containerid,c.containername,pb.productname,p.productid,ws.workflowstepid,ws.workflowstepname,w.workflowid,s.specid from container c
left join mfgorder m on m.mfgorderid=c.mfgorderid
left join product p on p.productid=c.productid
left join productbase pb on pb.productbaseid=p.productbaseid
left join currentstatus cu on cu.currentstatusid=c.currentstatusid
left join workflowstep ws on ws.workflowstepid=cu.workflowstepid
left join workflow w on w.workflowid=ws.workflowid 
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
where c.status=1 and c.parentcontainerid is null ";

            if (para.Keys.Contains("ContainerName")) {
                strSql += string.Format(" and c.containername='{0}' ",para["ContainerName"]);
            }
            if (para.Keys.Contains("ContainerID"))
            {
                strSql += string.Format(" and c.ContainerID='{0}' ", para["ContainerID"]);
            }
            return OracleHelper.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 获取派工信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetDispatchInfo(Dictionary<string, string> para) {

            string strSql = @" SELECT di.qty,ws.workflowstepname,t.teamname,di.plannedcompletiondate,
                   di.id,di.workflowid,di.specid,di.dispatchdate,e.fullname, 
                   r.resourcename,e1.fullname AS deName,di.containerid,
                   di.dispatchtype,DECODE(di.dispatchtype,0,'班组派工',1,'任务指派') AS disptype                  
            FROM dispatchinfo di
           left join workflowstep ws on ws.workflowstepid=di.workflowstepid
            LEFT JOIN team t ON t.teamid = di.teamid
            LEFT JOIN resourcedef r ON r.resourceid = di.resourceid
            LEFT JOIN employee e ON e.employeeid = di.dispatchemployeeid
            LEFT JOIN DispatchEmployeeInfo de ON de.dispatchinfoid = di.id
            LEFT JOIN employee e1 ON e1.employeeid = de.employeeid where 1=1 ";

            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"])) {
                strSql += string.Format(" and di.containerid='{0}'",para["ContainerID"]);
            }
            if (para.ContainsKey("WorkflowStepID") && !string.IsNullOrWhiteSpace(para["WorkflowStepID"]))
            {
                strSql += string.Format(" and di.WorkflowStepID='{0}'", para["WorkflowStepID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and di.SpecID='{0}'", para["SpecID"]);
            }

            strSql += " order by di.dispatchtype asc ";
            return OracleHelper.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 撤销派工数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
      public  ResultModel RevokeDispatchInfo(Dictionary<string, string> para) {
            ResultModel re = new ResultModel(true, "");
            //验证是否有报工数据
            ExcuteEntity excute = new ExcuteEntity("WorkReportInfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() {new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str), new FieldEntity("WorkflowStepID", para["WorkflowStepID"], FieldType.Str) };
            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0) {
                re=new ResultModel(false, "请先撤销报工数据");
                return re;
            }
            //撤销派工数据
            List<OracleParameter> oraParams= new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_stepID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowStepID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeDispatchInfo", oraParams.ToArray(),out result);

            if(result>0)
                re= new ResultModel(true, "撤销成功");

            return re;
        }

        /// <summary>
        /// 获取报工信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetWorkreportInfo(Dictionary<string, string> para) {
            string strSql = @"SELECT w.qty,sb.specname,ws.workflowstepname,t.teamname,w.workreportinfoid,w.specid,w.ReportDate,e.fullname,
                   r.resourcename,e.fullname AS deName,DECODE(w.ReportType,0,'首件',4,'外协','其他') AS rType,w.reporttype,
                   w.containerid,w.workflowid,s.unitworktime
            FROM WorkReportInfo w
            left join workflowstep ws on ws.workflowstepid=w.workflowstepid
            LEFT JOIN spec s ON s.specid = w.specid
            LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid
            LEFT JOIN DispatchInfo di ON di.id = w.DispatchInfoID
            LEFT JOIN team t ON t.teamid = di.teamid
            LEFT JOIN resourcedef r ON r.resourceid = w.resourceid
            LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID
            WHERE 1 = 1 ";

            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and w.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("WorkflowStepID") && !string.IsNullOrWhiteSpace(para["WorkflowStepID"]))
            {
                strSql += string.Format(" and di.WorkflowStepID='{0}'", para["WorkflowStepID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and di.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and w.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by w.Reportdate asc ";
            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 撤销报工数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeReportInfo(Dictionary<string, string> para)
        {
            ResultModel re = new ResultModel(true, "");
            //验证是否有外协的报工
            ExcuteEntity excute = new ExcuteEntity("WorkReportInfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str), new FieldEntity("WorkflowStepID", para["WorkflowStepID"], FieldType.Str)};
            excute.strWhere = " and synergicinfoid is not null ";
            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "存在外协报工，无法撤销");
                return re;
            }

            //验证是否有检验数据
            excute = new ExcuteEntity("WorkReportInfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str), new FieldEntity("WorkflowStepID", para["WorkflowStepID"], FieldType.Str),
            new FieldEntity("ischecked", 1, FieldType.Numer)};
            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "请先撤销检验的数据");
                return re;
            }
            //验证是否有数据采集数据
            excute = new ExcuteEntity("DataCollectDetailInfo dcd", ExcuteType.selectAll);
            excute.strWhere = string.Format(@" and dcd.checkvalue is not null and dcd.datacollectinfoid in(
            select dc.id from DataCollectInfo dc where dc.containerid='{0}' and dc.workflowid='{1}' and dc.specid='{2}')",
            para["ContainerID"], para["WorkflowID"], para["SpecID"]);
            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "存在工人录入的数据采集，请先撤销数据采集数据");
                return re;
            }

            //撤销报工数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_stepID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowStepID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeReportInfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }

        /// <summary>
        /// 获取检验数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetCheckDataInfo(Dictionary<string,string> para) {
            string strSql = @"SELECT w.qty AS rQty,e.fullname AS rName,w.ReportDate,
e1.fullname AS rcheckName,c.createdate checkdate,c.eligibilityqty,c.nonsenseqty,
decode(c.checktype,0,'首检',3,'工序检',4,'入厂检验') checktype
FROM ConventionCheckInfo c
LEFT JOIN WorkReportInfo w ON w.workreportinfoid = c.ReportInfoID
LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID
LEFT JOIN employee e1 ON e1.employeeid = c.checkemployeeid
WHERE 1 = 1 ";
            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and c.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and c.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and c.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by c.createdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 撤销检验数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeCheckInfo(Dictionary<string, string> para)
        {
            ResultModel re = new ResultModel(true, "");
            //验证是否有质量数据,数据采集项
            ExcuteEntity excute = new ExcuteEntity("qualityrecordinfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str),
                new FieldEntity("WorkflowID", para["WorkflowID"], FieldType.Str), new FieldEntity("SpecID", para["SpecID"], FieldType.Str)  };

            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "请先撤销质量的数据");
                return re;
            }

            //验证是否有数据采集项
            excute = new ExcuteEntity("DataCollectInfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str),
                new FieldEntity("WorkflowID", para["WorkflowID"], FieldType.Str), new FieldEntity("SpecID", para["SpecID"], FieldType.Str)  };
            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "请先撤销数据采集的数据");
                return re;
            }
            //撤销派工数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_workflowid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_specid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["SpecID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeCheckInfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }

        /// <summary>
        /// 获取数据采集数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetCollectDataInfo(Dictionary<string, string> para) {
            string strSql = @" SELECT  dc.*, dcd.productno,dcd.collectqty,dcd.checkvalue,decode(dcd.collecttype,1,'关键工序',2,'首检',3,'工序检验',4,'入长检验') collecttype,
                   ci.checkiteminfoname,ci.checkitem,dcd.checkitemid,dcd.SequenceNum,dcd.checkvalue1,dcd.checkvalue
            FROM DataCollectInfo dc
            LEFT JOIN DataCollectDetailInfo dcd ON dc.id = dcd.datacollectinfoid
            LEFT JOIN checkiteminfo ci ON ci.checkiteminfoid = dcd.checkitemid where 1=1 ";

            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and dc.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and dc.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and dc.WorkflowID='{0}'", para["WorkflowID"]);
            }

            strSql += " order by dc.Collectdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 撤销数据采集数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeDataCollectInfo(Dictionary<string, string> para)
        {
            ResultModel re = new ResultModel(true, "");
            //验证

            //撤销数据采集数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_workflowid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_specid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["SpecID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeDataCollectInfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }
        /// <summary>
        /// 获取质量数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetQualityDataInfo(Dictionary<string, string> para) {
            string strSql = @"SELECT q.*, DECODE(NVL(q.issubmit,0),0,'否',1,'是') disissubmit,
                   e.fullname
            FROM QualityRecordInfo q
            LEFT JOIN employee e ON e.employeeid = q.submitemployeeid where 1=1 ";
            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and q.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and q.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and q.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by q.submitdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 撤销质量数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeQualityInfo(Dictionary<string, string> para)
        {
            ResultModel re = new ResultModel(true, "");
            //验证如果提交不合格审理则提示
            ExcuteEntity excute = new ExcuteEntity("qualityrecordinfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str),
                new FieldEntity("WorkflowID", para["WorkflowID"], FieldType.Str), new FieldEntity("SpecID", para["SpecID"], FieldType.Str),new FieldEntity("issubmit", 1, FieldType.Numer)   };

            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "存在不合格审理，请先撤销不合格审理数据");
                return re;
            }

            //撤销数据采集数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_workflowid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_specid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["SpecID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("Revokequalityrecordinfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }

        /// <summary>
        /// 获取不合格审理数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetNonconDataInfo(Dictionary<string, string> para)
        {
            string strSql = @"select rr.qualityrecordinfoid,rp.* from rejectappinfo rp 
left join rejectappqrinfo rr on rr.rejectappinfoid=rp.id where 1=1 ";
            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and rp.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and rp.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and rp.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by rp.submitdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 撤销不合格审理数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeNonconInfo(Dictionary<string, string> para)
        {
            ResultModel re = new ResultModel(true, "");
            //验证如果有报废，返修单或质疑单不能撤销
            //验证如果有报废
            ExcuteEntity excute = new ExcuteEntity("scrapinfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str),
                new FieldEntity("WorkflowID", para["WorkflowID"], FieldType.Str), new FieldEntity("SpecID", para["SpecID"], FieldType.Str)   };

            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "存在报废数据，请先撤销报废的数据");
                return re;
            }
            //验证是否有返修
            excute = new ExcuteEntity("repairinfo", ExcuteType.selectAll);
            excute.WhereFileds = new List<FieldEntity>() { new FieldEntity("ContainerID", para["ContainerID"], FieldType.Str),
                new FieldEntity("WorkflowID", para["WorkflowID"], FieldType.Str), new FieldEntity("SpecID", para["SpecID"], FieldType.Str)   };
            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "存在返修数据，无法撤销");
                return re;
            }
            //验证是否有质疑单
            excute = new ExcuteEntity("questioninfo", ExcuteType.selectAll);
            excute.strWhere = $" and rejectappinfoid in (select id from rejectappinfo where containerid='{para["ContainerID"]}' and SpecID='{para["SpecID"]}' and workflowid='{para["WorkflowID"]}' ) ";

            if (OracleHelper.QueryDataByEntity(excute).Rows.Count > 0)
            {
                re = new ResultModel(false, "存在质疑单数据，请先撤销质疑单的数据");
                return re;
            }

            //撤销数据采集数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_workflowid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_specid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["SpecID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeNonconinfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }
        /// <summary>
        /// 获取报废信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetScrapDataInfo(Dictionary<string, string> para) {
            string strSql = @" SELECT sp.ScrapInfoName,e.fullname,spi.qty,sp.QualityRecordInfoID,sp.rejectappinfoid,
                   sp.SubmitDate,l.lossreasonname
            FROM ScrapInfo sp
            LEFT JOIN ScrapProductNoInfo spi ON spi.ScrapInfoID = sp.ID            
            LEFT JOIN employee e ON e.employeeid = sp.SubmitEmployeeID
            LEFT JOIN LossReason l ON l.lossreasonid = spi.LossReasonID where 1=1 ";
            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and sp.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and sp.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and sp.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by sp.submitdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 撤销报废数据 scrapinfo
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeScrapInfo(Dictionary<string, string> para)
        {
            ResultModel re = new ResultModel(true, "");
            //验证

            //撤销报废数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_workflowid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_specid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["SpecID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_stepid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowStepID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeScrapinfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }

        /// <summary>
        /// 获取返修数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetRepairDataInfo(Dictionary<string, string> para) {
            string strSql = @"select e.fullname,rp.* from repairinfo rp 
left join employee e on e.employeeid=rp.opremployeeid where 1=1 ";
            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and rp.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and rp.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and rp.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by rp.oprdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 获取质疑单数据
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetQuestionDataInfo(Dictionary<string, string> para)
        {
            string strSql = @"select qip.productno,e.fullname,qi.* from questioninfo qi 
left join questionproductnoinfo qip on qip.questioninfoid=qi.id
left join rejectappinfo rp on rp.id=qi.rejectappinfoid
left join employee e on e.employeeid=qi.opremployeeid  where 1=1 ";
            if (para.ContainsKey("ContainerID") && !string.IsNullOrWhiteSpace(para["ContainerID"]))
            {
                strSql += string.Format(" and rp.containerid='{0}'", para["ContainerID"]);
            }
            if (para.ContainsKey("SpecID") && !string.IsNullOrWhiteSpace(para["SpecID"]))
            {
                strSql += string.Format(" and rp.SpecID='{0}'", para["SpecID"]);
            }
            if (para.ContainsKey("WorkflowID") && !string.IsNullOrWhiteSpace(para["WorkflowID"]))
            {
                strSql += string.Format(" and rp.WorkflowID='{0}'", para["WorkflowID"]);
            }
            strSql += " order by qi.oprdate asc ";

            return OracleHelper.Query(strSql).Tables[0];
        }
        /// <summary>
        /// 撤销质疑单记录
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public ResultModel RevokeQuestionInfo(Dictionary<string, string> para) {
            ResultModel re = new ResultModel(true, "");
            //验证

            //撤销质疑单数据
            List<OracleParameter> oraParams = new List<OracleParameter>();

            OracleParameter oraParam = new OracleParameter("in_containerID", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["ContainerID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_workflowid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["WorkflowID"];
            oraParams.Add(oraParam);

            oraParam = new OracleParameter("in_specid", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["SpecID"];
            oraParams.Add(oraParam);
            
            oraParam = new OracleParameter("in_revokeEmp", OracleDbType.Varchar2);
            oraParam.Direction = ParameterDirection.Input;
            oraParam.Value = para["RevokeEmployeeID"];
            oraParams.Add(oraParam);

            int result = 0;
            OracleHelper.RunProcedure("RevokeQuestioninfo", oraParams.ToArray(), out result);

            if (result > 0)
                re = new ResultModel(true, "撤销成功");

            return re;
        }
    }
}
